package net.sf.rose.jdbc;

import java.sql.Blob;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import net.sf.rose.exception.SystemErrorCode;
import net.sf.rose.exception.SystemException;
import net.sf.rose.jdbc.annotation.cache.ORMConfigBean;
import net.sf.rose.jdbc.annotation.cache.ORMConfigBeanCache;
import net.sf.rose.jdbc.service.Service;

import org.apache.log4j.Logger;
import org.springframework.beans.BeanWrapper;
import org.springframework.beans.BeanWrapperImpl;

/**
 * 功能:数据库操作的封装类
 * @author fengjian
 * @since 2013/05/18
 */
public class GenericDAO {

	private static Logger logger = Logger.getLogger(GenericDAO.class);

	private GenericLog log = DBUtils.getLogClassInstance();

	/** 数据库服务类 */
	protected Service service = null;

	private Connection con = null;

	private PreparedStatement pstmt = null;

	private ResultSet rs = null;

	public GenericDAO() {
		super();
	}

	public GenericDAO(Service service) {
		super();
		this.setService(service);
	}

	/**
	 * 设置数据库服务类
	 * @param service - 数据库服务类 
	 */
	public void setService(Service service) {
		this.service = service;
		this.con = null;
	}

	/**
	 * 设置数据库连接
	 * @param con - 数据库连接 
	 */
	public void setConnection(Connection con) {
		this.con = con;
	}

	/**
	 * 功能:通过类的属性值取得数据类 
	 * <p> 如果查询没有结果，则返回null
	 * <p> 如果查询有多个结果，则第一条记录
	 * @return 返回取得的数据类
	 * @throws SystemException - 如果数据库操作失败
	 */
	public Object load(QuerySQL query) {
		try {
			if (this.con == null)
				this.con = service.getConnection();
			this.fillStatement(query.getSql(), query.getParameterList());
			rs = pstmt.executeQuery();
			Object obj = toDataBean(rs, query.getEntityClass());
			if (rs.next()) {
				String message = "查询符合条件的记录不止一条！";
				logger.error(log.getLog(this, query));
				SystemException ex = new SystemException(message, SystemErrorCode.D_SEARCH_ERROR);
				logger.error(message, ex);
				throw ex;
			}
			if (logger.isInfoEnabled()) {
				logger.info(log.getLogAll(this, query));
			}
			return obj;
		} catch (SystemException e) {
			throw e;
		} catch (Exception e) {
			String message = "数据库查询失败！";
			logger.error(log.getLog(this, query));
			logger.error(message, e);
			throw new SystemException(message, SystemErrorCode.D_SEARCH_ERROR);
		} finally {
			closeResult();
		}
	}

	/**
	 * 功能:通过SQL取得数据类列表
	 * @throws SystemException - 如果数据库操作失败
	 */
	public List<Object> list(QuerySQL query) {
		// 执行SQL语句
		try {
			if (this.con == null)
				this.con = service.getConnection();
			// 如果需要分页
			if (query.getPage() != null) {
				PageBean page = query.getPage();
				// 如果当前数据库是Mysql，则需要查出总记录数
				if (DBUtils.isMysqlDatabaseUsed()) {
					this.fillStatement(query.getCountSql(), query.getParameterList());
					rs = pstmt.executeQuery();
					if (rs.next()) {
						long count = rs.getLong(1);
						if (count != 0) {
							// 保存符合条件的记录数量
							page.setRecordCount(Integer.parseInt(count + ""));
						}
					}
				}
			}
			this.fillStatement(query.getSql(), query.getParameterList());
			rs = pstmt.executeQuery();
			List<Object> list = toDataListBean(rs, query);
			if (logger.isInfoEnabled()) {
				logger.info(log.getLogAll(this, query));
			}
			// 清空分析组件
			query.setPage(null);
			return list;
		} catch (SystemException e) {
			throw e;
		} catch (Exception e) {
			String message = "数据库查询失败！";
			logger.error(log.getLog(this, query));
			logger.error(message, e);
			throw new SystemException(message, SystemErrorCode.D_SEARCH_ERROR);
		} finally {
			closeResult();
		}
	}

	/**
	 * 功能:执行数据库更新
	 * @throws SystemException - 如果数据库操作失败
	 */
	public int update(QuerySQL query) {
		// 执行SQL语句
		try {
			if (this.con == null)
				this.con = service.getConnection();
			this.fillStatement(query.getSql(), query.getParameterList());
			int i = pstmt.executeUpdate();
			logger.warn(log.getLog(this, query));
			if (i == 0) {
				String message = "数据库更新操作没有影响到一条记录！";
				throw new SystemException(message, SystemErrorCode.D_UPDATE_ERROR);
			}
			return i;
		} catch (SystemException e) {
			throw e;
		} catch (Exception e) {
			String message = "数据库更新操作失败！";
			logger.error(log.getLog(this, query));
			logger.error(message, e);
			throw new SystemException(message, SystemErrorCode.D_UPDATE_ERROR);
		} finally {
			closeResult();
		}
	}

	/**
	 * 功能:执行数据库批量操作
	 * @return 操作结果值
	 * @throws SystemException - 如果数据库操作失败
	 */
	public int[] batchUpdate(QuerySQL query) {
		Object[] arr = null;
		try {
			if (this.con == null)
				this.con = service.getConnection();
			pstmt = con.prepareStatement(query.getSql());
			List<Object> list = query.getParameterList();
			for (int j = 0; j < list.size(); j++) {
				arr = (Object[]) list.get(j);
				for (int i = 0; i < arr.length; i++) {
					pstmt.setObject(i + 1, arr[i]);
				}
				pstmt.addBatch();
			}
			int[] i = pstmt.executeBatch();
			pstmt.close();
			logger.warn(log.getLog(this, query));
			return i;
		} catch (SystemException e) {
			throw e;
		} catch (SQLException e) {
			String message = "数据库批处理操作失败！";
			logger.error(log.getLog(this, query));
			logger.error(message, e);
			throw new SystemException(message, SystemErrorCode.D_BATCH_ERROR);
		} finally {
			closeResult();
		}
	}
	
	/**
	 * 功能:执行数据库批量操作
	 * @param count - 一次批量更新的记录数(大数据量的时候用)
	 * @return 操作结果值
	 * @throws SystemException - 如果数据库操作失败
	 */
	public void batchUpdate(QuerySQL query, int count) {
		Object[] arr = null;
		try {
			if (this.con == null)
				this.con = service.getConnection();
			pstmt = con.prepareStatement(query.getSql());
			List<Object> list = query.getParameterList();
			for (int j = 0; j < list.size(); j++) {
				arr = (Object[]) list.get(j);
				for (int i = 0; i < arr.length; i++) {
					pstmt.setObject(i + 1, arr[i]);
				}
				pstmt.addBatch();
				if (j % count == 0) {
					pstmt.executeBatch();
					pstmt.clearBatch();
				}
			}
			pstmt.executeBatch();
			pstmt.close();
			//logger.warn(super.sql);
		} catch (SystemException e) {
			throw e;
		} catch (SQLException e) {
			String message = "数据库大量数据批处理操作失败！";
			logger.error(log.getLog(this, query));
			logger.error(message, e);
			throw new SystemException(message, SystemErrorCode.D_BATCH_QUANTITY_ERROR);
		} finally {
			closeResult();
		}
	}

	/**
	 * 功能:将参数载入PrepareStatement类中
	 * @param sql - SQL语句
	 * @param list - 参数容器类
	 * @throws SQLException - 如果操作失败将抛出异常
	 */
	protected void fillStatement(String sql, List<Object> list) throws SQLException {
		pstmt = con.prepareStatement(sql);
		if (list != null && list.size() > 0) {
			int index = 1;
			for (Object o : list) {
				pstmt.setObject(index, o);
				index++;
			}
		}
	}

	/**
	 * 功能:关闭查询结果集
	 * <p> 包括PreparedStatement和ResultSet二个对象的关闭操作
	 * <p> 此方法执行时若发生异常将不被抛出。
	 */
	protected void closeResult() {
		try {
			if (rs != null) {
				rs.close();
				rs = null;
			}
			if (pstmt != null) {
				pstmt.close();
				pstmt = null;
			}
		} catch (SQLException e) {
			rs = null;
			pstmt = null;
		}
	}

	/**
	 * 功能:将数据库查询结果集中的数据列表加载入DataBean类，并封装至ArrayList类中。
	 * @param rs - ResultSet结果集
	 * @return List<DataBean>数据列表
	 * @throws Exception - 如果操作失败将抛出异常
	 */
	protected List<Object> toDataListBean(ResultSet rs, QuerySQL query) throws Exception {
		Class<?> entityClass = query.getEntityClass();
		String[] colnames = getColnames(rs);
		int columnCount = colnames.length;
		// 判断此次查询需要分页，且当前使用的数据库是Oracle
		int count = -1;
		PageBean page = query.getPage();
		if (page != null && DBUtils.isOracleDatabaseUsed()) {
			// 值为0表示需要取Oracle查询的总记录数
			count = 0;
			columnCount = columnCount - 2;
		}
		Object val = null;
		List<Object> list = new java.util.ArrayList<Object>();
		// 如果没有指定实体类，就使用通用的Map类型加载
		if (entityClass == null) {
			while (rs.next()) {
				Map<String, Object> data = new HashMap<String, Object>();
				if (count == 0) {
					// 取Oracle查询的总记录数
					count = Integer.parseInt(rs.getObject(columnCount + 1).toString());
					page.setRecordCount(count);
				}
				for (int i = 1; i <= columnCount; i++) {
					val = rs.getObject(i);
					if (val != null)
						data.put(colnames[i - 1], val);
				}
				list.add(data);
			}
		}
		// 如果指定了实体类
		else {
			// 将数据库字段名替换成类的字段名
			ORMConfigBean config = ORMConfigBeanCache.getORMConfigBean(entityClass);
			String[] propertyNames = new String[columnCount];
			for (int i = 0; i < columnCount; i++) {
				propertyNames[i] = config.getFieldNameByDBName(colnames[i]);
			}
			Object t = null;
			BeanWrapperImpl bw = new BeanWrapperImpl();
			while (rs.next()) {
				if (count == 0) {
					// 取Oracle查询的总记录数
					count = Integer.parseInt(rs.getObject(columnCount + 1).toString());
					page.setRecordCount(count);
				}
				t = entityClass.newInstance();
				bw.setWrappedInstance(t);
				for (int i = 1; i <= columnCount; i++) {
					if (bw.getPropertyType(propertyNames[i - 1]) == java.io.InputStream.class) {
						Blob b = rs.getBlob(i);
						if (b != null) {
							val = b.getBinaryStream();
						}
					} else {
						val = rs.getObject(i);
					}
					if (val != null)
						bw.setPropertyValue(propertyNames[i - 1], val);
				}
				list.add(t);
			}
		}
		// 如果需要分页，将结果数据集装入分页类中
		if (page != null) {
			page.setRecords(list);
		}
		return list;
	}

	/**
	 * 功能:将数据库查询结果集中的数据加载入DataBean类中。
	 * @param rs - ResultSet结果集
	 * @return DataBean数据类
	 * @throws Exception - 如果操作失败将抛出异常
	 */
	protected Object toDataBean(ResultSet rs, Class<?> entityClass) throws Exception {
		if (!rs.next()) {
			return null;
		}
		Object t = null;
		Object val = null;
		String[] colnames = getColnames(rs);
		// 如果没有指定实体类，就使用通用的Map类型加载
		if (entityClass == null) {
			Map<String, Object> data = new HashMap<String, Object>();
			for (String key : colnames) {
				val = rs.getObject(key);
				if (val != null) {
					data.put(key, val);
				}
			}
			t = data;
		}
		// 如果指定了实体类
		else {
			// 将数据库字段名替换成类的字段名
			int columnCount = colnames.length;
			ORMConfigBean config = ORMConfigBeanCache.getORMConfigBean(entityClass);
			String[] propertyNames = new String[columnCount];
			for (int i = 0; i < columnCount; i++) {
				propertyNames[i] = config.getFieldNameByDBName(colnames[i]);
			}
			t = entityClass.newInstance();
			BeanWrapper bw = new BeanWrapperImpl(t);
			for (int i = 1; i <= columnCount; i++) {
				if (bw.getPropertyType(propertyNames[i - 1]) == java.io.InputStream.class) {
					Blob b = rs.getBlob(i);
					if (b != null) {
						val = b.getBinaryStream();
					}
				} else {
					val = rs.getObject(i);
				}
				if (val != null)
					bw.setPropertyValue(propertyNames[i -1], val);
			}
		}
		return t;
	}

	/**
	 * 
	 * 功能:返回结果集的元数据名数组。
	 * @param rs - ResultSet结果集
	 * @return String[]列名称数据
	 * @throws SQLException - 如果操作失败将抛出异常
	 */
	private String[] getColnames(ResultSet rs) throws SQLException {
		ResultSetMetaData rsmd = rs.getMetaData();
		int columnCount = rsmd.getColumnCount();
		String[] colnames = new String[columnCount];
		for (int i = 1; i <= columnCount; i++) {
			colnames[i - 1] = rsmd.getColumnName(i);
		}
		return colnames;
	}

}
